package com.lvwenguo.service.serviceImple;


import com.lvwenguo.dao.CategoryDao;
import com.lvwenguo.dao.CategoryPurchaseListDao;
import com.lvwenguo.dao.PurchaseInputDao;
import com.lvwenguo.dao.SupplierListDao;
import com.lvwenguo.model.CategoryPurchaseList;
import com.lvwenguo.model.InputProductPurchase;
import com.lvwenguo.model.SupplierList;
import com.lvwenguo.service.PurchaseServiceInter;
import com.lvwenguo.utils.JdbcUtil;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

//实现接口
public class PurchaseServiceImple implements PurchaseServiceInter {
    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private Connection connection;
    private PurchaseInputDao purchaseInputDao;
    private JdbcUtil jdbcUtil;
    private CategoryPurchaseListDao categoryPurchaseListDao;
    private SupplierListDao supplierListDao;
    public PurchaseServiceImple(){
        connection=JdbcUtil.getJdbcUtil().getConnertion();
      purchaseInputDao=new PurchaseInputDao(connection);
      categoryPurchaseListDao=new CategoryPurchaseListDao(connection);
      supplierListDao=new SupplierListDao(connection);
    }
    //实现供应商用户名查询
    @Override
    public List<InputProductPurchase> selectSupplierName() {
        String sql="select s.supplier_name from biz_supplier s";
        try {
            return purchaseInputDao.selectList(sql,InputProductPurchase.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //实现品类名数据查询
    @Override
    public List<InputProductPurchase> selectCategoryName() {
        String sql="select c.category_name from biz_category c";
        try {
            return purchaseInputDao.selectList(sql,InputProductPurchase.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //商品录入执行方法（重点）
    @Override
    public String InputProductData(InputProductPurchase inputProductPurchase, String sessionname) {

        /*
         * 思路分析
         * 1、查询条形码和商品名称是否存在重复
         *      分两种情况：1、没有相同的商品信息 2、有相同的商品信息
         *         1、没有相同的商品信息
         *             1、添加商品信息
         *                 1、根据供应商名称获取id
         *                 2、根据品类名称获取id
         *             2、商品录入表信息
         *                 1、查询新添加商品id
         *             3、库存表信息更新
         *                 2、进货数量=更新库存信息
         *         2、有相同的商品信息
         *            1、更新商品信息表信息
         *            2、添加商品录入表信息
         *            3、更新库存表
         *            ////（没有做）
         *            4、库存修改记录表需要操作
         * */
        try {
            connection.setAutoCommit(false);//开启事务
            //查询条形码和商品名称是否存在重复
            String sql="select p.id from biz_product p where p.bar_code=? and p.product_name=?";
            Object[] objects={inputProductPurchase.getBar_code(), inputProductPurchase.getProduct_name()};
            List<InputProductPurchase> i = purchaseInputDao.selectList(sql,objects,InputProductPurchase.class);
            //表示有相同信息
            if(i.size()!=0){
                //更新商品信息表数据
                String updateProduct="UPDATE biz_product p set p.sale_price=?,p.update_time=? where p.bar_code=?";
                Object[] objects1={inputProductPurchase.getSale_price(), sdf.parse(sdf.format(new Date())), inputProductPurchase.getBar_code()};
                int updateP = purchaseInputDao.update(updateProduct,objects1);
                //表示更新成功
                if(updateP>0){
                    //查询商品录入后的id
                    String sqlselectProductID="select p.id from biz_product p where p.bar_code=?";
                    Object[] objects2={inputProductPurchase.getBar_code()};
                    List<InputProductPurchase> ProductId = purchaseInputDao.selectList(sqlselectProductID,objects2,InputProductPurchase.class);
                    //商品录入表添加信息
                    String purchaseSql="INSERT into biz_purchase (product_id,purchase_date,pro_date,exp_date,purchase_price,count,amount,receipt_img,create_time,update_time,deleted) values(?,?,?,?,?,?,?,?,?,?,?);";
                    Object[] objects3={ProductId.get(0).getId(), sdf.parse(sdf.format(inputProductPurchase.getPurchase_date())), sdf.parse(sdf.format(inputProductPurchase.getPro_date())),
                            sdf.parse(sdf.format(inputProductPurchase.getExp_date())), inputProductPurchase.getPurchase_price(),
                            inputProductPurchase.getCount(), inputProductPurchase.getCount() * inputProductPurchase.getPurchase_price(), "",
                            sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0};
                    int Addpurchase = purchaseInputDao.Add(purchaseSql,objects3);
                    //表示商品录入表信息添加成功
                    if(Addpurchase>0){
                        //查询现有库存
                        String selectCount="select s.stock_count from biz_stock s where s.product_bar_code=?";
                        Object[] objects4={inputProductPurchase.getBar_code()};
                        List<InputProductPurchase> Count =purchaseInputDao.selectList(selectCount,objects4,InputProductPurchase.class);
                        System.out.println(Count.get(0).getStock_count());
                        //更新库存信息表
                        String updatestock="update biz_stock s set s.stock_count=?,s.update_time=? where s.product_bar_code=?";
                       Object[] objects5={(inputProductPurchase.getCount() + Count.get(0).getStock_count()),sdf.parse(sdf.format(new Date())), inputProductPurchase.getBar_code()};
                        int updates = purchaseInputDao.update(updatestock,objects5);
                        //表示库存表更新成功
                        if(updates>0){
                            //查询用户id
                            String selectsessionnameId="select u.id from sys_user u where u.login_account=?";
                            Object[] objects6={sessionname};
                            List<InputProductPurchase> UserId = purchaseInputDao.selectList(selectsessionnameId,objects6,InputProductPurchase.class);
                            //添加库存修改记录表数据
                            String AddstockModifyRecord="INSERT into biz_stock_modify_record (product_bar_code,operator_id,modify_count,modify_type,create_time,update_time,deleted) values(?,?,?,?,?,?,?)";
                            Object[] objects7={inputProductPurchase.getBar_code(), UserId.get(0).getId(), inputProductPurchase.getCount(), "修改", sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0};
                            int upd = purchaseInputDao.Add(AddstockModifyRecord,objects7);
                            //表示成功添加
                            if(upd>0){
                                //提交事务
                                connection.commit();
                                return "成功修改";
                            }
                            else{
                                //回滚事务
                                try {
                                    connection.rollback();
                                } catch (SQLException ex) {
                                    ex.printStackTrace();
                                }
                                return "库存修改记录更新失败";
                            }

                        }else{
                            //回滚事务
                            try {
                                connection.rollback();
                            } catch (SQLException ex) {
                                ex.printStackTrace();
                            }
                            return "库存信息更新失败";
                        }

                    }
                    else{
                        //回滚事务
                        try {
                            connection.rollback();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                        return "商品录入记录表更新失败";
                    }
                }
                else{
                    //回滚事务
                    try {
                        connection.rollback();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    return "商品信息修改失败";
                }

            }
            //表示没有相同信息
            else{
                //查询供应商id
                String sqlsupplierId="select s.id from biz_supplier s where s.supplier_name=?";
                Object[] objects1={ inputProductPurchase.getSupplier_name()};
                List<InputProductPurchase> supplierId = purchaseInputDao.selectList(sqlsupplierId,objects1,InputProductPurchase.class);
                /* 查询 */
                String sqlcategoryId="select c.id from biz_category c where c.category_name=?";
                Object[] objects2={inputProductPurchase.getCategory_name()};
                List<InputProductPurchase> categoryId = purchaseInputDao.selectList(sqlcategoryId,objects2,InputProductPurchase.class);
                //添加商品信息
                String AddProaductSql="INSERT into biz_product (bar_code,supplier_id,category_id,product_name,sale_price,create_time," +
                        "update_time,deleted)VALUES(?,?,?,?,?,?,?,?);";
                Object[] objects3={ inputProductPurchase.getBar_code(),
                        supplierId.get(0).getId(), categoryId.get(0).getId(), inputProductPurchase.getProduct_name(),
                        inputProductPurchase.getSale_price(), sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0};
                int AddProduct = purchaseInputDao.Add(AddProaductSql,objects3);
                //添加成功
                if(AddProduct>0){
                    //查询商品录入后的id
                    String sqlselectProductID="select p.id from biz_product p where p.bar_code=?";
                    Object[] objects4={inputProductPurchase.getBar_code()};
                    List<InputProductPurchase> ProductId =purchaseInputDao.selectList(sqlselectProductID,objects4,InputProductPurchase.class);
                    //商品录入表更新
                    String purchaseSql="INSERT into biz_purchase (product_id,purchase_date,pro_date,exp_date,purchase_price,count,amount,receipt_img,create_time,update_time,deleted) values(?,?,?,?,?,?,?,?,?,?,?);";
                    Object[] objects5={ProductId.get(0).getId(), sdf.parse(sdf.format(inputProductPurchase.getPurchase_date())), sdf.parse(sdf.format(inputProductPurchase.getPro_date())),
                            sdf.parse(sdf.format(inputProductPurchase.getExp_date())), inputProductPurchase.getPurchase_price(),
                            inputProductPurchase.getCount(), inputProductPurchase.getCount() * inputProductPurchase.getPurchase_price(), "",
                            sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0};
                    int Addpurchase = purchaseInputDao.Add(purchaseSql,objects5);
                    //商品录入表数据更新完成
                    if(Addpurchase>0){
                        //库存信息更新
                        String sqlstock="INSERT into biz_stock (product_bar_code,stock_count,create_time,update_time,deleted,lock_key) values(?,?,?,?,?,?)";
                        Object[] objects6={ inputProductPurchase.getBar_code(), inputProductPurchase.getCount(), sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0, ""};
                        int stock = purchaseInputDao.update(sqlstock,objects6);
                        //表示功能完成
                        if(stock>0){
                            //查询用户id
                            String selectsessionnameId="select u.id from sys_user u where u.login_account=?";
                            Object[] objects7={sessionname};
                            List<InputProductPurchase> UserId = purchaseInputDao.selectList(selectsessionnameId,objects7,InputProductPurchase.class);
                            //添加库存修改记录表数据
                            String AddstockModifyRecord="INSERT into biz_stock_modify_record (product_bar_code,operator_id,modify_count,modify_type,create_time,update_time,deleted) values(?,?,?,?,?,?,?)";
                            Object[] objects8={inputProductPurchase.getBar_code(), UserId.get(0).getId(), inputProductPurchase.getCount(), "修改", sdf.parse(sdf.format(new Date())), sdf.parse(sdf.format(new Date())), 0};
                            int upd = purchaseInputDao.Add(AddstockModifyRecord,objects8);
                            //表示成功添加
                            if(upd>0){
                                //提交事务
                                connection.commit();
                                return "成功修改";
                            }
                        }else{
                            //回滚事务
                            try {
                                connection.rollback();
                            } catch (SQLException ex) {
                                ex.printStackTrace();
                            }
                            return "库存信息添加失败";
                        }
                    }
                    else{
                        //回滚事务
                        try {
                            connection.rollback();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                        return "商品录入表添加失败";
                    }

                }else{
                    //回滚事务
                    try {
                        connection.rollback();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    return "商品信息添加失败";
                }

            }
        } catch (Exception e) {
            //回滚事务
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return null;
    }
    //进货记录数据回显
    @Override
    public List<CategoryPurchaseList> selectCategoryPurchaseList() {
        String sql="select pu.id,su.supplier_name,pr.bar_code,ca.category_name,pr.product_name,pu.purchase_price,pu.count,pu.amount,pu.exp_date from biz_purchase pu,biz_product pr,biz_supplier su,biz_category ca where pu.product_id=pr.id and pr.supplier_id=su.id and pr.category_id=ca.id and pu.deleted=0";
        try {
            return categoryPurchaseListDao.selectList(sql,CategoryPurchaseList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //进货记录页面模糊查询
    @Override
    public List<CategoryPurchaseList> selectMohu(String name) {
        String sql="select pu.id,su.supplier_name,pr.bar_code,ca.category_name,pr.product_name,pu.purchase_price,pu.count,pu.amount,pu.exp_date from biz_purchase pu,biz_product pr,biz_supplier su,biz_category ca where pu.product_id=pr.id and pr.supplier_id=su.id and pr.category_id=ca.id and pu.deleted=0 and pr.product_name like ?";
        Object[] objects={name};
        try {
            return categoryPurchaseListDao.selectList(sql,objects,CategoryPurchaseList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //用于异步交互实现数据查询
    public CategoryPurchaseList selectYibu(String bar_code) {
        String sql="select pu.id,su.supplier_name,pr.bar_code,ca.category_name,pr.product_name,pu.purchase_price,pu.count,pu.amount,pu.exp_date from biz_purchase pu,biz_product pr,biz_supplier su,biz_category ca where pu.product_id=pr.id and pr.supplier_id=su.id and pr.category_id=ca.id and pu.deleted=0 and pr.bar_code=?";
        Object[] objects={bar_code};
        try {
            return categoryPurchaseListDao.selectOnt(sql,objects,CategoryPurchaseList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //供应商信息查询（初始化页面）
    @Override
    public List<SupplierList> selectSupplier() {
        String sql="select s.id,s.supplier_name,s.contacts_name,s.tel,s.address from biz_supplier s where s.deleted=0";
        try {
            return supplierListDao.selectList(sql,SupplierList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //用于模糊查询信息
    @Override
    public List<SupplierList> selectSupplierMohu(String name) {
        String sql="select s.id,s.supplier_name,s.contacts_name,s.tel,s.address from biz_supplier s where s.deleted=0\n" +
                "and \n" +
                "s.supplier_name like ?";
        Object[] objects={"%"+name+"%"};
        try {
            return supplierListDao.selectList(sql,objects,SupplierList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //用于添加供应商信息
    @Override
    public int addSupplier(SupplierList supplierList) {
        String selectsql="select s.id,s.supplier_name,s.contacts_name,s.tel,s.address from biz_supplier s where s.deleted=0 and s.supplier_name=?";
        Object[] objects={supplierList.getSupplier_name()};
        try {
            List<SupplierList> lists = supplierListDao.selectList(selectsql, objects, SupplierList.class);
            if(lists.size()!=0){
                return 0;
            }else{
                //添加供应商信息
                String AddSupplier="INSERT into biz_supplier (supplier_name,contacts_name,tel,address,create_time,update_time,deleted) values(?,?,?,?,?,?,?)";
                Object[] objects1={supplierList.getSupplier_name(),supplierList.getContacts_name(),supplierList.getTel(),
                        supplierList.getAddress(),sdf.parse(sdf.format(new Date())),sdf.parse(sdf.format(new Date())),0};
                return supplierListDao.Add(AddSupplier,objects1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }
    //用于数据回显
    @Override
    public SupplierList selectSup(int id) {
        String selectsql="select s.id,s.supplier_name,s.contacts_name,s.tel,s.address from biz_supplier s where s.deleted=0 and s.id=?";
        Object[] objects={id};
        try {
            return supplierListDao.selectOnt(selectsql,objects,SupplierList.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //更新供应商信息
    @Override
    public int updateSupplier(SupplierList supplierList) {
        SupplierList supplierList1 = selectSup(supplierList.getId());
        if(supplierList1.getId()!=0){
            String sql="update biz_supplier set supplier_name=?,contacts_name=?,tel=?,address=? where id=?";
           Object[] objects={supplierList.getSupplier_name(),supplierList.getContacts_name(),supplierList.getTel(),supplierList.getAddress(),supplierList.getId()};
            try {
                return  supplierListDao.update(sql,objects);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }else{
            return 0;
        }
        return 0;
    }
    //模拟删除
    @Override
    public int deleteSupplier(int id) {
        String sql="UPDATE biz_supplier set deleted=1 where id=?";
        Object[] objects={id};
        try {
            return supplierListDao.update(sql,objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
